﻿using lyAdmin.Entity.Bean;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Text;

namespace lyAdmin.Repository
{
    public class SqlHelper
    {
        public static void getListSql( QueryBean queryBean, ref string sql,ref List<MySqlParameter> param)
        {

            for (int i = 0; i < queryBean.Fields.Count; i++)
                if (queryBean.Fields[i].Value != null)
                {
                    string type = "";
                    switch (queryBean.Fields[i].Type)
                    {
                        case "neq": type = "!="; break;
                        case "gt": type = ">"; break;
                        case "egt": type = "<"; break;
                        case "lt": type = "<"; break;
                        case "elt": type = "<="; break;
                        case "like": type = "like"; break;
                        default: type = "="; break;
                    }
                    if (queryBean.Fields[i].Type == "between")
                    {
                        string[] date = queryBean.Fields[i].Value.ToString().Split(',');
                        sql += String.Format(" and `{0}` between '{1}' and '{2}'", queryBean.Fields[i].Field, date[0], date[1]);
                    }
                    else if (queryBean.Fields[i].Type == "in")
                    {
                        sql += String.Format(" and `{0}` in (@{0})", queryBean.Fields[i].Field);
                        param.Add(new MySqlParameter() { ParameterName = queryBean.Fields[i].Field, Value = queryBean.Fields[i].Value });
                    }
                    else
                    {
                        sql += String.Format(" and `{0}` {1} @{0}", queryBean.Fields[i].Field, type);
                        param.Add(new MySqlParameter() { ParameterName = queryBean.Fields[i].Field, Value = queryBean.Fields[i].Value });
                    }
                }
                else if (queryBean.Fields[i].Field.ToLower() == "state")
                    sql += " and state > -1 ";
            if (queryBean.Sort != null)
                sql += " order by " + queryBean.Sort.Field + " " + queryBean.Sort.Type;
            if (queryBean.Page.Size > 0)
                sql += String.Format(" LIMIT {0},{1}", queryBean.Page.Size * (queryBean.Page.Page - 1), queryBean.Page.Size);
         
        }
    }
}
